package pms;

import java.awt.Component;
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;

import javax.swing.JOptionPane;

public class Database
{
	String	db;
	String	user;
	String	pw;

	public Database() throws IOException
	{
		db = "cs341g1";
		user = "cs341g1";
		pw = "y833678";
	}

	public Database(String infoFile) throws IOException
	{
		BufferedReader in = new BufferedReader(new FileReader(infoFile));
		db = in.readLine();
		user = in.readLine();
		pw = in.readLine();
		
	}

	public void updateInfo(String command)
	{
		try
		{
			Class.forName("com.mysql.jdbc.Driver"); // register the driver
			String dbURL = "jdbc:mysql://cs341.cs.uwosh.edu:4381/" + db;
			//System.out.println("Attempting connection");
			Connection conn = DriverManager.getConnection(dbURL, user, pw);
			//System.out.println("Connection successful");
			Statement st = conn.createStatement();
			st.executeUpdate(command); // creates table
			conn.close();
		}
		catch (SQLException e)
		{
			Object frame = null;
			JOptionPane.showMessageDialog((Component) frame, e.getMessage());
		}
		catch (Exception e)
		{
			Object frame = null;
			JOptionPane.showMessageDialog((Component) frame, e.getMessage());
		}
	}

	public ResultSet query(String query)
	{
		ResultSet rs = null;
		Connection conn;
		try
		{
			Class.forName("com.mysql.jdbc.Driver"); // register the driver
			String dbURL = "jdbc:mysql://cs341.cs.uwosh.edu:4381/" + db;
			//System.out.println("Attempting connection");
			conn = DriverManager.getConnection(dbURL, user, pw);
			
			Statement st = conn.createStatement();
			rs = st.executeQuery(query);
			
			//conn.close();
			
			return rs;
		}
		catch (SQLException e)
		{
			Object frame = null;
			JOptionPane.showMessageDialog((Component) frame, e.getMessage());
		}
		catch (Exception e)
		{
			Object frame = null;
			JOptionPane.showMessageDialog((Component) frame, e.getMessage());
		}
		
		return null;
	}
	
	public ResultSet select(String columns, String tables, String where) throws Exception {
		columns = (columns.equals("")) ? "*" : columns;
		if (tables.equals("")) {
			throw new Exception("Must have valid tables.");
		}
		ResultSet rs;
		if (where.equals("")) {
//			System.out.println("SELECT " + columns + " FROM " + tables);
			rs = query( "SELECT " + columns + " FROM " + tables );
		} else {
//			System.out.println("SELECT " + columns + " FROM " + tables + " WHERE " + where);
			rs = query( "SELECT " + columns + " FROM " + tables + " WHERE " + where );
		}
		return rs;
		
	}
	
	public ResultSet select(String columns, String tables, String order_column, String order_param) throws Exception {
		columns = (columns.equals("")) ? "*" : columns;
		if (tables.equals("")) {
			throw new Exception("Must have valid tables.");
		}
		ResultSet rs;
		if (order_column.equals("")) {
//			System.out.println("SELECT " + columns + " FROM " + tables);
			rs = query( "SELECT " + columns + " FROM " + tables );
		} else {
//			System.out.println("SELECT " + columns + " FROM " + tables + " ORDER BY " + order_column + " " + order_param );
			rs = query( "SELECT " + columns + " FROM " + tables + " ORDER BY " + order_column + " " + order_param );
		}
		return rs;
		
	}
	
	public ResultSet select(String columns, String tables, String where, String order_column, String order_param) throws Exception {
		columns = (columns.equals("")) ? "*" : columns;
		if (tables.equals("")) {
			throw new Exception("Must have valid tables.");
		}
		ResultSet rs;
		if (order_column.equals("")) {
//			System.out.println("SELECT " + columns + " FROM " + tables);
			rs = query( "SELECT " + columns + " FROM " + tables );
		} else if (where.equals("")){
//			System.out.println("SELECT " + columns + " FROM " + tables + " ORDER BY " + order_column + " " + order_param );
			rs = query( "SELECT " + columns + " FROM " + tables + " ORDER BY " + order_column + " " + order_param );
		} else {
//			System.out.println("SELECT " + columns + " FROM " + tables + " WHERE " + where + " ORDER BY " + order_column + " " + order_param );
			rs = query( "SELECT " + columns + " FROM " + tables + " WHERE " + where + " ORDER BY " + order_column + " " + order_param );
		}
		return rs;
		
	}
	
	public void insert(String table, String columns,String values) throws Exception {
		columns = (columns.equals("")) ? "*" : columns;
		if (table.equals("")) {
			throw new Exception("Must have valid table.");
		}
		if (columns.equals("")) {
//			System.out.println("INSERT INTO "+table+ " VALUES ("+values+")");
			updateInfo( "INSERT INTO "+table+ " VALUES ("+values+")");
		} else {
			System.out.println("INSERT INTO "+table+" ("+columns+")"+" VALUES ("+values+")");
			updateInfo( "INSERT INTO "+table+" ("+columns+")"+" VALUES ("+values+")");
		}
	}
	
	public void editSingleColumn(String table, String value, String column, String where) throws Exception {
		if (table.equals("")) {
			throw new Exception("Must have valid table.");
		}
		if (column.equals("") || where.equals("")) {
			throw new Exception("Must have valid where and column.");
		} else {
			//System.out.println("UPDATE "+table+ " SET "+value+"="+column+" WHERE "+where);
			updateInfo("UPDATE "+table+ "SET "+value+"="+column+" WHERE "+where);
		}
	}

	public void update (String tables, String set, String qualification) {
//		System.out.println("UPDATE " + tables + " SET " + set + " WHERE " + qualification);
		updateInfo("UPDATE " + tables + " SET " + set + " WHERE " + qualification);
	}

	public void delete(String tables, String qualification) {
		// TODO Auto-generated method stub
		// DELETE FROM table_name WHERE some_column=some_value
//		System.out.println("DELETE FROM " + tables + " WHERE " + qualification);
		updateInfo("DELETE FROM " + tables + " WHERE " + qualification);
	}
}